package com.ruoyi.web.controller.sujian;

import com.ruoyi.common.config.Global;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.system.domain.SysFloor;
import com.ruoyi.system.domain.TenantInfo;
import com.ruoyi.system.domain.TotalFeeInfo;
import com.ruoyi.system.mapper.SysFloorMapper;
import com.ruoyi.system.mapper.TenantInfoMapper;
import com.ruoyi.system.mapper.TotalFeeMapper;
import com.ruoyi.system.service.ITotalFeeService;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.io.ResourceUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 总费用管理
 *
 * @author chengxl
 * @date 2021-07-05
 */
@Controller
@RequestMapping("/sujian/totalCostManagement")
public class TotalCostManController extends BaseController
{
    private String prefix = "propertys/sujian/totalCostManagement";
    private static String downLoadTemplate = "classpath:download/total_fee.xlsx";
    private enum EXPLORER_TYPE{IE, FIREFOX, CHROME, EDGE, OTHER};
    private Integer totalFeeTemplateColumnNum = 19;


    @Autowired
    private SysFloorMapper sysFloorMapper;

    @Autowired
    private TenantInfoMapper tenantInfoMapper;

    @Autowired
    private TotalFeeMapper totalFeeMapper;

    @Autowired
    private ITotalFeeService iTotalFeeService;

    @GetMapping()
    public String totalCostMan(ModelMap modelMap)
    {
        List<SysFloor> sysFloors = sysFloorMapper.selectSysFloorListAll(new SysFloor());
        modelMap.put("floorInfos",sysFloors);
        List<TenantInfo> tenantInfos = tenantInfoMapper.selectAllTenantInfo();
        modelMap.put("tenantInfos",tenantInfos);
        return prefix + "/main";
    }
    /**
     * 查询总费用信息
     */
    @PostMapping("/list")
    @ResponseBody
    public TableDataInfo list(TotalFeeInfo totalFeeInfo)
    {
        startPage();
        String etlDt = totalFeeInfo.getEtlDt();
        String sjfq;
        if(StringUtils.isNotEmpty(etlDt)){
            if(etlDt.length()==6){
                sjfq = etlDt.replaceAll("-","").substring(0,6);
            }else{
                sjfq = "197001";
            }
            totalFeeInfo.setSjfq(Integer.valueOf(sjfq));
        }
        List<TotalFeeInfo> list = totalFeeMapper.selectAllTotalFeeInfos(totalFeeInfo);
        return getDataTable(list);
    }

    @PostMapping("/createTotalFee")
    @ResponseBody
    /**
     * etlDt:日期
     */
    public AjaxResult createTotalFee(String etlDt) {
        //传入的etldt必须不为空，前端控制
        //如果后台有正在运行的作业，提示该操作无效
        if (null == etlDt || "".equals(etlDt.trim())) {
            logger.error("日期没有从前台传过来。");
            return AjaxResult.error("请选择重新计算的年月日期。");
        }
        if (etlDt.contains("-")) {
            etlDt = etlDt.replaceAll("-", "");
        }
        etlDt = etlDt.substring(0,6);
        iTotalFeeService.createTotalFee(etlDt);
        return AjaxResult.success(etlDt+"总费用重新计算完成。");
    }

    /**
     * 导出选择年月的总费用信息
     * @param request
     * @param resp
     * @return
     * @throws Exception
     */
    @PostMapping("/exportTotalFee")
    @ResponseBody
    public AjaxResult exportTotalFee(HttpServletRequest request, HttpServletResponse resp) throws Exception {
        String etldt = request.getParameter("etlDt");
        try {
            if(StringUtils.isNotEmpty(etldt)){
                etldt = etldt.replaceAll("-","").substring(0,6);
            }else{
                return AjaxResult.error("缺少日期");
            }
        }catch(Exception e){
        }
        String fileName = "total_fee_"+etldt +".xlsx";
        String fileNamePath = Global.getDownloadPath()+"/"+fileName;
        File desc = new File(fileNamePath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        try {
            desc.createNewFile();
        }catch(IOException ie){
            ie.printStackTrace();
        }
        FileOutputStream fos = null;
        TotalFeeInfo totalFeeInfo = new TotalFeeInfo();
        totalFeeInfo.setSjfq(Integer.valueOf(etldt));
        List<TotalFeeInfo> totalFeeInfos= totalFeeMapper.selectAllTotalFeeInfos(totalFeeInfo);

        try {
            fos = new FileOutputStream(fileNamePath);
            Workbook workbook = dataobjectStrategyExportExcelTemplate(downLoadTemplate,totalFeeInfos, totalFeeTemplateColumnNum);
            workbook.write(fos);
        }catch(Exception e){
            e.printStackTrace();
            return AjaxResult.error("导出总费用信息失败。");
        }finally{
            try {
                fos.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return AjaxResult.success("success",fileName);
    }

    /**
     * 导出选择年月的总费用信息
     * @param request
     * @param resp
     * @return
     * @throws Exception
     */
    @GetMapping("/importTotalFeeTemplate")
    @ResponseBody
    public AjaxResult importTotalFeeTemplate(HttpServletRequest request, HttpServletResponse resp) throws Exception {
        String fileName = "totalfee.xlsx";
        String fileNamePath = Global.getDownloadPath()+"/"+fileName;
        File desc = new File(fileNamePath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        try {
            desc.createNewFile();
        }catch(IOException ie){
            ie.printStackTrace();
        }
        FileOutputStream fos = null;

        try {
            fos = new FileOutputStream(fileNamePath);
            Workbook workbook = dataobjectStrategyExportExcelTemplate(downLoadTemplate,null, totalFeeTemplateColumnNum);
            workbook.write(fos);
        }catch(Exception e){
            e.printStackTrace();
            return AjaxResult.error(e.getMessage());
        }finally{
            try {
                fos.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return AjaxResult.success("success",fileName);
    }

    //下载总费用excel
    @GetMapping("/downloadTotalFeeExcel/{fileName}")
    @ResponseBody
    public void downloadTotalFeeExcel(HttpServletRequest request, HttpServletResponse response, @PathVariable("fileName") String fileName){
        String fileNamePath = Global.getDownloadPath()+"/"+fileName;
        File desc = new File(fileNamePath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        try {
            download(desc.getName(), fileNamePath, request, response);
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                FileUtils.forceDelete(desc);
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }

    @PostMapping("/importTotalFeeData")
    @ResponseBody
    public AjaxResult importTotalFeeData(String etldt,MultipartFile file, boolean updateSupport) throws Exception
    {
        try {
            if(StringUtils.isNotEmpty(etldt)){
                etldt = etldt.replaceAll("-","").substring(0,6);
            }else{
                return AjaxResult.error("缺少日期");
            }
        }catch(Exception e){
        }
        try {
            iTotalFeeService.importTotalFeeByEtlDt(etldt, file, totalFeeTemplateColumnNum);
        }catch(Exception e){
            e.printStackTrace();
            return AjaxResult.error("导入总费用信息失败。");
        }
        return AjaxResult.success("导入总费用信息成功。");
    }

    public static void download(String fileName, String realFileName,
                                HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        InputStream input = org.apache.commons.io.FileUtils.openInputStream(new File(realFileName));
        byte[] data = IOUtils.toByteArray(input);
        fileName = encoderName(request.getHeader("User-Agent"), fileName);
        response.reset();
        response.setHeader("Content-Disposition", "attachment; filename=\""
                + fileName + "\"");
        response.addHeader("Content-Length", "" + data.length);
        response.setContentType("application/octet-stream; charset=UTF-8");
        IOUtils.write(data, response.getOutputStream());
        IOUtils.closeQuietly(input);
    }

    private static String encoderName(String userAgent, String name) throws UnsupportedEncodingException {
        EXPLORER_TYPE type = getExplorerType(userAgent);
        if (type == EXPLORER_TYPE.FIREFOX || type == EXPLORER_TYPE.CHROME) {
            name = new String(name.getBytes("UTF-8"), "ISO8859-1");
        } else if (type == EXPLORER_TYPE.IE || type == EXPLORER_TYPE.EDGE) {
            name = URLEncoder.encode(name, "UTF-8");
        }
        return name;
    }

    private static EXPLORER_TYPE getExplorerType(String userAgent){
        userAgent = userAgent.toUpperCase();
        if(userAgent.indexOf("MSIE")>0){
            return EXPLORER_TYPE.IE;
        }else if(userAgent.indexOf("FIREFOX") >0){
            return EXPLORER_TYPE.FIREFOX;
        }else if(userAgent.indexOf("CHROME") > 0){
            return EXPLORER_TYPE.CHROME;
        }else if(userAgent.indexOf("EDGE") > 0){
            return EXPLORER_TYPE.CHROME;
        }else if(userAgent.indexOf("GECKO") >0){//没有其他标识，则判断为IE11
            return EXPLORER_TYPE.IE;
        }else{
            return EXPLORER_TYPE.OTHER;
        }
    }

    public static Workbook dataobjectStrategyExportExcelTemplate(String realFileName,List<TotalFeeInfo> totalFeeInfos, Integer totalFeeTemplateColumnNum)throws Exception{
        Workbook workbook = new XSSFWorkbook(ResourceUtils.getInputStreamForPath(realFileName));
        Sheet sheet = workbook.getSheetAt(0);
        if(null != totalFeeInfos && (totalFeeInfos.size() > 0)) {
            int i = 0;
            double[] feeSum = new double[totalFeeTemplateColumnNum];
            for (i = 0; i < totalFeeInfos.size(); i++) {
                Row row = sheet.getRow(i + 1);
                if (row == null) row = sheet.createRow(i + 1);
                getCell(row, 0).setCellValue(totalFeeInfos.get(i).getFloorName());//楼层
                getCell(row, 1).setCellValue(totalFeeInfos.get(i).getTenantName());//单位
                getCell(row, 2).setCellValue(totalFeeInfos.get(i).getTenantArea());//建筑面积
                getCell(row, 3).setCellValue(totalFeeInfos.get(i).getWgFee());//物管费
                getCell(row, 4).setCellValue(totalFeeInfos.get(i).getWaterNum());//水表数
                getCell(row, 5).setCellValue(totalFeeInfos.get(i).getWaterFee());//水费
                getCell(row, 6).setCellValue(totalFeeInfos.get(i).getElectricNum());//电表数
                getCell(row, 7).setCellValue(totalFeeInfos.get(i).getZhaomingNum());//实际用电，即照明电量
                getCell(row, 8).setCellValue(totalFeeInfos.get(i).getZhaomingFee());//照明费
                getCell(row, 9).setCellValue(totalFeeInfos.get(i).getDongliNum());//动力用电
                getCell(row, 10).setCellValue(totalFeeInfos.get(i).getDongliFee());//动力费
                getCell(row, 11).setCellValue(totalFeeInfos.get(i).getGongyongNum());//公用电
                getCell(row, 12).setCellValue(totalFeeInfos.get(i).getGongyongFee());//公用电费
                getCell(row, 13).setCellValue(totalFeeInfos.get(i).getKongtiaoNum());//空调电量
                getCell(row, 14).setCellValue(totalFeeInfos.get(i).getKongtiaoFee());//空调电费
                getCell(row, 15).setCellValue(totalFeeInfos.get(i).getDianfeiTotal());//电费小计
                getCell(row, 16).setCellValue(totalFeeInfos.get(i).getKongtiaoOilFee());//空调油费
                getCell(row, 17).setCellValue(totalFeeInfos.get(i).getOilTotal());//油费合计
                getCell(row, 18).setCellValue(totalFeeInfos.get(i).getFeeTotal());//合计


                feeSum[2] = feeSum[2] + totalFeeInfos.get(i).getTenantArea();
                feeSum[3] = feeSum[3] + totalFeeInfos.get(i).getWgFee();
                feeSum[4] = feeSum[4] + totalFeeInfos.get(i).getWaterNum();
                feeSum[5] = feeSum[5] + totalFeeInfos.get(i).getWaterFee();
                feeSum[6] = feeSum[6] + totalFeeInfos.get(i).getElectricNum();
                feeSum[7] =  feeSum[7] + totalFeeInfos.get(i).getZhaomingNum();
                feeSum[8] =  feeSum[8] + totalFeeInfos.get(i).getZhaomingFee();
                feeSum[9] =  feeSum[9] + totalFeeInfos.get(i).getDongliNum();
                feeSum[10] = feeSum[10] + totalFeeInfos.get(i).getDongliFee();
                feeSum[11] = feeSum[11] + totalFeeInfos.get(i).getGongyongNum();
                feeSum[12] = feeSum[12] + totalFeeInfos.get(i).getGongyongFee();
                feeSum[13] = feeSum[13] + totalFeeInfos.get(i).getKongtiaoNum();
                feeSum[14] = feeSum[14] + totalFeeInfos.get(i).getKongtiaoFee();
                feeSum[15] = feeSum[15] + totalFeeInfos.get(i).getDianfeiTotal();
                feeSum[16] = feeSum[16] + totalFeeInfos.get(i).getKongtiaoOilFee();
                feeSum[17] = feeSum[17] + totalFeeInfos.get(i).getOilTotal();
                feeSum[18] = feeSum[18] + totalFeeInfos.get(i).getFeeTotal();
            }
            //写入最后一行合计行
            Row row = sheet.getRow(i + 1);
            if (row == null) row = sheet.createRow(i + 1);
            DecimalFormat df = new DecimalFormat("#.00");
            getCell(row, 0).setCellValue("合计");//楼层
            getCell(row, 2).setCellValue(df.format(feeSum[2]));//建筑面积
            getCell(row, 3).setCellValue(df.format(feeSum[3]));//物管费
            getCell(row, 4).setCellValue(df.format(feeSum[4]));//水表数
            getCell(row, 5).setCellValue(df.format(feeSum[5]));//水费
            getCell(row, 6).setCellValue(df.format(feeSum[6]));//电表数
            getCell(row, 7).setCellValue(df.format(feeSum[7]));//实际用电，即照明电量
            getCell(row, 8).setCellValue(df.format(feeSum[8]));//照明费
            getCell(row, 9).setCellValue(df.format(feeSum[9]));//动力用电
            getCell(row, 10).setCellValue(df.format(feeSum[10]));//动力费
            getCell(row, 11).setCellValue(df.format(feeSum[11]));//公用电
            getCell(row, 12).setCellValue(df.format(feeSum[12]));//公用电费
            getCell(row, 13).setCellValue(df.format(feeSum[13]));//空调电量
            getCell(row, 14).setCellValue(df.format(feeSum[14]));//空调电费
            getCell(row, 15).setCellValue(df.format(feeSum[15]));//电费小计
            getCell(row, 16).setCellValue(df.format(feeSum[16]));//空调油费
            getCell(row, 17).setCellValue(df.format(feeSum[17]));//油费合计
            getCell(row, 18).setCellValue(df.format(feeSum[18]));//合计
        }
        return workbook;
    }

    private static Cell getCell(Row row, int cellNo){
        Cell cell = null;
        cell = row.getCell(cellNo);
        if(cell==null){
            cell = row.createCell(cellNo);
        }
        return cell;
    }
    /**
     * 将Date类型转换为字符串
     *
     * @param date
     *            日期类型
     * @param pattern
     *            字符串格式
     * @return 日期字符串
     */
    public static String format(Date date, String pattern) {
        if (date == null) {
            return "null";
        }
        if (pattern == null || pattern.equals("") || pattern.equals("null")) {
            pattern = "yyyy-MM-dd HH:mm:ss";
        }
        return new java.text.SimpleDateFormat(pattern).format(date);
    }

}
